package com.zxpt.quetion.utils;

import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 所在包：com.huizi.dev.common.utils
 * 文件名：
 * 项目：login
 * 功能描述：
 *    此段描述很关键，一定要认真研读
 *      1.每个条件的拼接都需要重新传入字段名称
 *      2.如果mapper中的传值对象名称不为map，可以通过resetMapName方法重新设置，必须在条件拼接之前操作
 *      3.所有匹配具体值的方法都以Value结尾，非Value结尾的操作方法都是从传值对现象中获取
 * 修改人：Xlz
 * 修改时间：2020-05-21 9:03
 */
public class Query {
    // 查询sql
    private String sql = "";
    // sql存放数组
    private List<String> conditionList = new ArrayList<>();
    // 当前类字段名
    private String fieldName;
    // 当前数据库字段名
    private String columnName;
    // mapper中传值对象名称（如果是多参数传递）
    private String mapName = "map.";
    // 查询条件数据
    private Map map;
    // 是否忽略空数据判断条件的添加
    private Boolean isIgnoreNull = true;
    // 别名
    private String alianName = "";

    public Query(String sql, Map map) {
        this.sql = sql;
        this.map = map;
    }

    /**
     * 获取sql方法
     * @return
     */
    public String getSql() {
        for (String piece: conditionList) {
            sql = sql + piece + " ";
        }
        return sql;
    }

    /**
     * 重新设置传值对象名称
     * @param mapName
     * @return
     */
    public Query resetMapName(String mapName) {
        this.mapName = mapName + ".";
        return this;
    }

    /**
     * 是否忽略空数据判断（默认忽略空数据条件添加，每个判断条件都需要指定）
     * @param isIgnoreNull
     * @return
     */
    public Query isIgnoreNull(Boolean isIgnoreNull) {
        if (isIgnoreNull == null)
            this.isIgnoreNull = true;
        else
            this.isIgnoreNull = isIgnoreNull;
        return this;
    }

    /**
     * 逻辑与
     * @return
     */
    public Query and() {
        String condition =  "and";
        conditionList.add(condition);
        return this;
    }

    /**
     * 逻辑或
     * @return
     */
    public Query or() {
        String condition = "or";
        conditionList.add(condition);
        return this;
    }

    /**
     * 前括号
     * @return
     */
    public Query begin() {
        String condition = "(";
        conditionList.add(condition);
        return this;
    }

    /**
     * 后括号
     * @return
     */
    public Query end() {
        String condition = ")";
        conditionList.add(condition);
        return this;
    }

    /**
     * 表别名
     * @param alian
     * @return
     */
    public Query alian(String alian) {
        alianName = alian + ".";
        return this;
    }

    /**
     * 当前操作字段名
     * @param fieldName
     * @return
     */
    public Query field(String fieldName) {
        this.fieldName = fieldName;
        this.columnName = getColumnByField(fieldName);
        return this;
    }

    /**
     * 等于
     * @return
     */
    public Query equal() {
        String condition = alianName + columnName + " =  #{" + mapName + fieldName + "}" ;
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 等于
     * @param newName
     * @return
     */
    public Query equal(String newName) {
        String condition = alianName + columnName + "= #{" + mapName + newName + "}";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 等于
     * @param value
     * @return
     */
    public Query equalValue(Number value) {
        String condition = alianName + columnName + " = " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 等于
     * @param value
     * @return
     */
    public Query equalValue(String value) {
        String condition = alianName + columnName + " = \"" + value + "\"";
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 前模糊
     * @return
     */
    public Query pLike() {
        String condition = alianName + columnName + " like concat('%', #{" + mapName + fieldName + "})";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 前模糊
     * @param newName
     * @return
     */
    public Query pLike(String newName) {
        String condition = alianName + columnName + " like concat('%', #{" + mapName + newName + "})";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 前模糊
     * @param value
     * @return
     */
    public Query pLikeValue(String value) {
        String condition = alianName + columnName + " like \"%" + value + "\"";
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 后模糊
     * @return
     */
    public Query lLike() {
        String condition = alianName + columnName + " like concat(#{" + mapName + fieldName + "}, '%')";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 后模糊
     * @param newName
     * @return
     */
    public Query lLike(String newName) {
        String condition = alianName + columnName + " like concat(#{" + mapName + newName + "}, '%')";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 后模糊
     * @param value
     * @return
     */
    public Query lLikeValue(String value) {
        String condition = alianName + columnName + " like \"" + value + "%\"";
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 前后模糊
     * @return
     */
    public Query bLike() {
        String condition = alianName + columnName + " like concat('%',#{" + mapName + fieldName + "}, '%')";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 前后模糊
     * @param newName
     * @return
     */
    public Query bLike(String newName) {
        String condition = alianName + columnName + " like concat('%',#{" + mapName + newName + "}, '%')";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 前后模糊
     * @param value
     * @return
     */
    public Query bLikeValue(String value) {
        String condition = alianName + columnName + " like \"%" + value + "%\"";
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于
     * @return
     */
    public Query greater() {
        String condition = alianName + columnName + " > #{" + mapName + fieldName + "}";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于
     * @param newName
     * @return
     */
    public Query greater(String newName) {
        String condition = alianName + columnName + " > #{" + mapName + newName + "}";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于
     * @param value
     * @return
     */
    public Query greaterValue(String value) {
        String condition = alianName + columnName + " > " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于
     * @param value
     * @return
     */
    public Query greaterValue(Number value) {
        String condition = alianName + columnName + " > " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于
     * @return
     */
    public Query less() {
        String condition = alianName + columnName + " < #{" + mapName + fieldName + "}";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于
     * @param newName
     * @return
     */
    public Query less(String newName) {
        String condition = alianName + columnName + " < #{" + mapName + newName + "}";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于
     * @param value
     * @return
     */
    public Query lessValue(String value) {
        String condition = alianName + columnName + " < " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于
     * @param value
     * @return
     */
    public Query lessValue(Number value) {
        String condition = alianName + columnName + " < " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于等于
     * @return
     */
    public Query greaterEqual() {
        String condition = alianName + columnName + " >= #{" + mapName + fieldName + "}";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于等于
     * @param newName
     * @return
     */
    public Query greaterEqual(String newName) {
        String condition = alianName + columnName + " >= #{" + mapName + newName + "}";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于等于
     * @param value
     * @return
     */
    public Query greaterEqualValue(String value) {
        String condition = alianName + columnName + " >= " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 大于等于
     * @param value
     * @return
     */
    public Query greaterEqualValue(Number value) {
        String condition = alianName + columnName + " >= " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于等于
     * @return
     */
    public Query lessEqual() {
        String condition = alianName + columnName + " <= #{" + mapName + fieldName + "}";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于等于
     * @param newName
     * @return
     */
    public Query lessEqual(String newName) {
        String condition = alianName + columnName + " <= #{" + mapName + newName + "}";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于等于
     * @param value
     * @return
     */
    public Query lessEqualValue(String value) {
        String condition = alianName + columnName + " <= " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 小于等于
     * @param value
     * @return
     */
    public Query lessEqualValue(Number value) {
        String condition = alianName + columnName + " <= " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 不等于
     * @return
     */
    public Query notEqual() {
        String condition = alianName + columnName + " <> #{" + mapName + fieldName + "}";
        if (isIgnoreNull) {
            Object value = map.get(fieldName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 不等于
     * @param newName
     * @return
     */
    public Query notEqual(String newName) {
        String condition = alianName + columnName + " <> #{" + mapName + newName + "}";
        if (isIgnoreNull) {
            Object value = map.get(newName);
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 不等于
     * @param value
     * @return
     */
    public Query notEqualValue(String value) {
        String condition = alianName + columnName + " <> " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 不等于
     * @param value
     * @return
     */
    public Query notEqualValue(Number value) {
        String condition = alianName + columnName + " <> " + value;
        if (isIgnoreNull) {
            if (value==null || StringUtils.isEmpty(value.toString().trim())) {
                conditionList.add("true");
            } else {
                conditionList.add(condition);
            }
        } else {
            conditionList.add(condition);
        }
        resetParam();
        return this;
    }

    /**
     * 为空
     * @return
     */
    public Query isNull() {
        String condition = alianName + columnName + " is null ";
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * 不为空
     * @return
     */
    public Query isNotNull() {
        String condition = alianName + columnName + " is not null ";
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * in操作（值为字符串）
     * @return
     */
    public Query inString() {
        Object value = map.get(fieldName);
        String inData = "";
        String condition = alianName + columnName;
        if (value == null || StringUtils.isEmpty(value.toString().trim())) {
            if (isIgnoreNull) {
                condition = condition + " and true";
            } else {
                condition = condition + " and false";
            }
        } else {
            String[] arr = value.toString().split(",");
            for (String v : arr) {
                if (!StringUtils.isEmpty(v.trim())) {
                    inData = inData + ",\"" + v.trim() + "\"";
                }
            }
            if (inData.length() > 1) {
                condition = condition + " in (" + inData.substring(1) + ")";
            } else {
                condition = condition + " and true";
            }
        }
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * in操作（值为字符串）
     * @return
     */
    public Query inString(String newName) {
        Object value = map.get(newName);
        String inData = "";
        String condition = alianName + columnName;
        if (value == null || StringUtils.isEmpty(value.toString().trim())) {
            if (isIgnoreNull) {
                condition = condition + " and true";
            } else {
                condition = condition + " and false";
            }
        } else {
            String[] arr = value.toString().split(",");
            for (String v : arr) {
                if (!StringUtils.isEmpty(v.trim())) {
                    inData = inData + ",\"" + v.trim() + "\"";
                }
            }
            if (inData.length() > 1) {
                condition = condition + " in (" + inData.substring(1) + ")";
            } else {
                condition = condition + " and true";
            }
        }
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * in操作（值为字符串）
     * @return
     */
    public Query inStringValue(String value) {
        String inData = "";
        String condition = alianName + columnName;
        if (value == null || StringUtils.isEmpty(value.toString().trim())) {
            if (isIgnoreNull) {
                condition = condition + " and true";
            } else {
                condition = condition + " and false";
            }
        } else {
            String[] arr = value.toString().split(",");
            for (String v : arr) {
                if (!StringUtils.isEmpty(v.trim())) {
                    inData = inData + ",\"" + v.trim() + "\"";
                }
            }
            if (inData.length() > 1) {
                condition = condition + " in (" + inData.substring(1) + ")";
            } else {
                condition = condition + " and true";
            }
        }
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * in操作（值为数字）
     * @return
     */
    public Query inNumber() {
        Object value = map.get(fieldName);
        String inData = "";
        String condition = alianName + columnName;
        if (value == null || StringUtils.isEmpty(value.toString().trim())) {
            if (isIgnoreNull) {
                condition = condition + " and true";
            } else {
                condition = condition + " and false";
            }
        } else {
            String[] arr = value.toString().split(",");
            for (String v : arr) {
                if (!StringUtils.isEmpty(v.trim())) {
                    inData = inData + "," + v.trim() + "";
                }
            }
            if (inData.length() > 1) {
                condition = condition + " in (" + inData.substring(1) + ")";
            } else {
                condition = condition + " and true";
            }
        }
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * in操作（值为数字）
     * @return
     */
    public Query inNumber(String newName) {
        Object value = map.get(newName);
        String inData = "";
        String condition = alianName + columnName;
        if (value == null || StringUtils.isEmpty(value.toString().trim())) {
            if (isIgnoreNull) {
                condition = condition + " and true";
            } else {
                condition = condition + " and false";
            }
        } else {
            String[] arr = value.toString().split(",");
            for (String v : arr) {
                if (!StringUtils.isEmpty(v.trim())) {
                    inData = inData + "," + v.trim() + "";
                }
            }
            if (inData.length() > 1) {
                condition = condition + " in (" + inData.substring(1) + ")";
            } else {
                condition = condition + " and true";
            }
        }
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * in操作（值为数字）
     * @return
     */
    public Query inNumberValue(String value) {
        String inData = "";
        String condition = alianName + columnName;
        if (value == null || StringUtils.isEmpty(value.toString().trim())) {
            if (isIgnoreNull) {
                condition = condition + " and true";
            } else {
                condition = condition + " and false";
            }
        } else {
            String[] arr = value.toString().split(",");
            for (String v : arr) {
                if (!StringUtils.isEmpty(v.trim())) {
                    inData = inData + "," + v.trim() + "";
                }
            }
            if (inData.length() > 1) {
                condition = condition + " in (" + inData.substring(1) + ")";
            } else {
                condition = condition + " and true";
            }
        }
        conditionList.add(condition);
        resetParam();
        return this;
    }

    /**
     * 重置数据
     */
    private void resetParam() {
        this.fieldName = null;
        this.columnName = null;
        this.isIgnoreNull = true;
        this.alianName = "";
    }

    /**
     * 根据类字段名获取表字段名称
     * @param filedName
     * @return
     */
    private String getColumnByField(String filedName) {
        String columnName = CreateQueryConditionUtil.transCamelToUnderline(filedName);
        return columnName;
    }
}
